A.C.E. 2

home *** CD-ROM | disk | FTP | other *** search

/ A.C.E. 2 / ACE CD 2.iso / FILES / DOCS / SBASEP4D.LHA / SuperBaseProDatabaseLang2.doc < prev next >

Wrap

Text File | 1994-11-27 | 98.2 KB | 3,956 lines

SUPERBASE PROFESSIONAL DATABASE MANAGEMENT LANGUAGE USER GUIDE Part II WHERE conditions allows you to add a filter to the command.conditions is set up in the same way as the command string in the Filter dialog. USING parameters can be used to change the default parameters specified by SET-OPTIONS. For a description of USING and the parameters it takes, see EXPORT. EXAMPLES 1 IMPORT "aaa.exp" 2 IMPORT "aaa.exp" TO FILE "aaa" WHERE (datefield) > DAYS ("29 Apr 87") INDEX PURPOSE Selects the index to be used with the current file. SYNTAX INDEX index COMMENTS Superbase automatically selects the first index as default when you open a file or when you make an open file the current file. With the INDEX command, you can select another of the file's indexes. index is the name of a field in the current file. An index for this field must already exist. Because DML parses entire lines before executing them, you cannot place an Index statement-or any other statement that refers to a field-on the same line as the OPEN FILE statement. When the DML interpreter reaches the INDEX statement, the file will not yet be open, and the field name, therefore, will not be recognized. As a result, a line like OPEN FILE "aaa": INDEX anum will produce an error message such as "Can't do this" or "Can't find this field". EXAMPLES 1 OPEN FILE "aaa" INDEX anum 5-70 2 OPEN FILE "aaa": OPEN FILE "bbb" ..... ..... FILE "aaa": INDEX anum ..... FILE "bbb": INDEX datea INPUT PURPOSE Reads characters or a line from a text file on disk into a variable or a field. SYNTAX INPUT[&nexp[, ]/LINE]var/field COMMENTS This statement inputs data from a text file on disk or from another computer using the COMMS link. It assumes that an input channel has been opened for example, by OPEN FOR INPUT. With the LINE option, INPUT reads data from the input channel until it finds a line feed character (ASCII 10) and inputs the data into var or field. If the line feed character has been used as the record separator, INPUT LINE reads a record at a time. var can be a string or numeric variable and field can be a text, numeric or date field. However, in each case the type of variable or field used should match the type of data expected. Thus if you attempt to input alphabetic characters into a numeric variable they will be valued at zero, while inputting them into a date field will produce an invalid date error. &nexp specifies the number of characters that the command takes from the text file. The comma after nexp is entirely optional. When nexp is zero or when & nexp is not used, the command reads characters from the text file until it finds a comma (ASCII 44) or a line feed character (ASCII 10). This option allows INPUT to be used instead of IMPORT to read in data from an exported file. To detect the end of a text file, use EOF ("*"). 5-71 EXAMPLES 1 INPUT LINE a$ 2 INPUT &6a$ (or INPUT &6, a$) 3 INPUT a% 4 FILE "aaa": EXPORT TO "aaa.exp" OPEN "aaa.exp" FOR INPUT FOR i%=1 TO RECCOUNT ("aaa") BLANK INPUT field1 INPUT field2 ........ INPUT lastfield STORE NEXT i%: CLOSE INPUT Example 4 demonstrates how INPUT can be used as an alternative to IMPORT. INSTR PURPOSE Returns the starting character position of a substring within a string, or returns 0 if the substring is not contained within the string. SYNTAX INSTR[nexpr, ]strexpr, substrexpr) COMMENTS This function returns the position in strexpr of the first occurrence of substrexpr. If nexpr is used, INSTR searches for the first occurrence of substrexpr from position nexpr onwards. If the substring is not found, the value returned is zero. nexpr must be positive and less than the length of strexpr. INSTR is case sensitive, i.e., "abc" and "ABC" are different. 5-72 EXAMPLES 1 x%=INSTR (x$, y$) 2 x%=INSTR (textfield, "Mr") 3 x%=INSTR (x$, "Mr"): y%=INSTR (x% + 1, x$, "Mr") 4 ? LEFT$ (textfield, INSTR (textfield, " ") -1) 5 x$=LCASE$ (textfield): x%=INSTR (x$, "abc") IF x% > 0 THEN ? LEFT$ (textfield, x% -1) NOTES The second example simply locates the title 'Mr.'. Example 3 locates any 'Mr and Mrs' or 'Mr & Mrs'. Example 4 displays the first word in textfield. Example 5 finds any occurrence of the sequence "abc" in textfield regardless of what case the letters are in. 5-73 INT PURPOSE Removes the part of a number to the right of the decimal point, turning it into a whole number. SYNTAX INT (nexpr) COMMENTS INT does not round a decimal number up or down to the nearest whole number, but simply strips off the decimal part. Thus INT (123.00001) and INT (123.999999) give the same result -123.0. EXAMPLES 1 numfieldc=INT (numfielda) 2 numfieldc=INT (numfielda * (1 + numfield) /100 3 $x=INT (VAL (RIGHT$ (textfield, 2)) /3.33 4 $x=INT ($y) 5 ? INT ($x ^ Sy) 5-74 KEY PURPOSE Displays the current set of function key definitions, or defines a new set. SYNTAX KEY keynum[, string] COMMENTS For the GEM versions of Superbase Professional running under MS DOS, the available function keys are F1 to F10. Each of these can be used with SHIFT, CONTROL OR ALT, giving 40 function keys in all. The numbers associated with the keys are: F1-F10 1-10 SHIFT (F1-F10) 11-20 CONTROL (F1-F10) 21-30 ALT (F1-F10) 31-40 On the AMIGA and the Atari ST, 21 function keys are available: the keys F1 to F10, which can be used with SHIFT, and the HELP key (HELP and Shift HELP are the same). Their numbers are: F1 to F10 1-10 SHIFT (F1-F10) 11-20 HELP 0 string can be any set of Superbase commands which can be entered on one line (provided that they do not access a field on the same line as an OPEN FILE). The command line in string is assigned to the key specified by keynum Using KEY without a following command string clears the key associated with keynum. If keynum and string and not used, KEY displays the current set of function key definitions. EXAMPLES 1 KEY 1 2 KEY 1, "OPEN FILE ~aaa~: BLANK: ENTER: STORE: ? ~ Now ~; RECCOUNT (~aaa~); ~Records~" 5-75 3 KEY 4 KEY 3, "Tel. (0428) 725400 [(04203) 5601 evenings]" Example 1 clears key F1. Example 2 sets F3 to enter a record into a file and report how many records there now are. Note the use of the tilde character to insert quotation marks. Since the entire string must be enclosed in quotation marks, you cannot use quotation marks within the string. 5-76 LABELS PURPOSE Prints records as labels. SYNTAX LABELS [FILE sbfname] [WHERE conditions] [USING labelparams] COMMENTS This command is the program equivalent of selecting the Labels option from the PROCESS menu. It allows you print out records as labels and to define their format. WHERE conditions limits the records for which labels are printed and acts as a filter. USING allows you to specify the shape and content of the labels to be printed. labelparams is a series of parameters separated by commas, relating to the label definition dialog. Reading down the left column of the dialog and then down the right column, they are as follows: line 1 fields/line line 2 fields/line line 3 fields/line line 4 fields/line line 5 fields/line line 6 fields/line line 7 fields/line line 8 fields/line line 9 fields/line line 10 fields/line First label margin label text width Second label margin First line next label Copies per label Labels per line Note: All 16 parameters must be used. If USING is not specified, LABELS takes the default parameters as shown in the label definition dialog (see Volume 1, Chapter 5). 5-77 EXAMPLES 1 LABELS WHERE CITY="London" USING 1, 0, 2, 1, 1, 1, 1, 1, 0, 0, 1, 35, 40, 12, 1, 2 2 LABELS "Address" WHERE Lastname LIKE "[a-c}*" LCASE$ PURPOSE Converts a text string to lowercase. SYNTAX LCASE$ (strexpr) COMMENTS This function changes upper case letters to lower case; no other characters, including those already in lowercase, are affected. The complementary function of LCASE$ is UCASE$. EXAMPLES 1 textfieldc=LCASE$ (textfielda) 2 x$=LCASE$ (y$) 3 x$=LCASE$ ("ABCDEF") 4 ? LCASE$ (x$) LEFT$ PURPOSE Extracts one or more characters from a string, starting at the left of the string. SYNTAX LEFT$ (strexpr, nexpr) 5-78 COMMENTS LEFT$ returns the leftmost nexpr characters of the string strexpr. Thus, if strexpr is DICTIONARY and expr is 7: LEFT$ ("DIRECTIONARY", 7) returns DICTION. EXAMPLES 1 textfieldc=LEFT$ (textfielda, 10) 2 textfieldc=UCASE$ (LEFT$ (textfielda, 1)) + MID$ (testfielda, 2) 3 LEFT$ (textfielda, 1) LIKE [a-c] 4 x$=LEFT$ ("ABCD", 2) 5 x$=LEFT$ (x$, n$) 6 ? LEFT$ (x$, 10) 7 strip: IF LEFT$ (x$, 1) =" " THEN x$=MID$ (x$, 2): GOTO strip NOTES Example 7 is a one line program to strip leading spaces from x$ (see LTRIM$). 5-79 LEN PURPOSE Returns the number of characters in a text string or text field. SYNTAX LEN (strexpr) COMMENTS LEN counts the number of characters in a string, including spaces and non- printing characters. EXAMPLES 1 numfieldc=LEN (textfielda) 2 textfieldc=RIGHT$ (textfielda, LEN (testfieldb) 3 LEN (textfielda) > 25 AND LEN (textfielda) 50 4 x$=LEN ("A") 5 x$=LEN (x$) 6 x$=LEN (MID$ (extfield, 3)) 7 ? LEN (x$) LET PURPOSE Assign a value to a variable. SYNTAX [LET] var/field=expr/[expr1 ? expra: exprb] COMMENTS The keyword LET is optional and is usually omitted. In other words, to assign a value to a variable or a field, you only need to use the equal sign. 5-80 The LET option has been included in DML to maintain compatibility with earlier versions of Basic. DML also provides a more unusual facility when you use the syntax: var/field=expr1 ? expra: exprab This option provides a short way of assigning different values to a variable or field, depending on whether an expression is true or not; that is, it makes assignment conditional on the truth or falsity of a specified expression. If expr1 is true, expra is assigned to the variable or field; if it is false, exprb is assigned. It is equivalent to IF expr1 THEN var/field=expra ELSE var/field=exprab Used in this way, the question mark character is referred to as a 'ternary operator' to reflect the fact that three operands are required at the right of the equal sign. In fact, you can chain ternary operators together to create a statement which contains multiple conditions and assigns one of multiple values. Within a program, however, you will generally find it easier to use the IF THEN ELSE statement. The main application for the ternary operator is in a Superbase file definition. Here it has an important advantage over the IF THEN ELSE statement: it can be entered as a calculation formula for a field in a file definition. For a fuller discussion of the ternary operator and its applications, see Chapter 2, Volume 1. EXAMPLES 1 item$="Sprocket" 2 Textfielda="London" 3 b%=3.25 4 numfieldb=INT (277/62) The following examples assume that x$="ABC" and x%=4.5 5 y$= (x$="ABC") ? "yes": "no" This example assigns "yes" to y$. 5-81 6 y$= (x$="aaa") ? x$: "" Assigns "" to y$. 7 y$= (x% > 3.5) ? "yes": "no" Assigns "yes" to y$ 8 y$=EOF ("INPUT") ? "end of file": "more to read" LIST PURPOSE Lists a text file to the screen. SYNTAX LIST filename COMMENTS LIST is the program equivalent of the LIST option in the SYSTEM menu. It displays a text file on the screen. Do not confuse LIST with ? LIST, which displays a program listing. LIST only works with text files. Note also that unlike many other DML commands, LIST requires the file name itself plus its extension name. EXAMPLES 1 LIST "aaa.exp" 2 LIST "Address.sbd" 5-82 LOAD PURPOSE Loads any of the following types of file into memory: programs, text files, function key files, Query and Update files. SYNTAX LOAD [TEXT]/[KEY]/[QUERY]/[UPDATE]filename[, APPEND] COMMENTS If none of the options TEXT, KEY, QUERY or UPDATE is used, Superbase assumes that filename refers to a '.sbp' file and attempts to load a PROGRAM file. The APPEND option can be used with program files and text files to append a file on disk to the file in memory. Note that LOAD cannot be used to load a Superbase data file (a file with an '.sbf' extension). For this you need to use the OPEN FILE command. EXAMPLES 1 LOAD "Program1" 2 LOAD TEXT "Banklet" 3 LOAD QUERY "Deptran" 4 LOAD KEY "Funkey1" 5 LOAD TEXT "Document2", APPEND 5-83 LOCATE PURPOSE Sets the position at which the next output appears on the current output device (generally, the screen or the printer). SYNTAX LOCATE column[, row] COMMENTS When you are displaying something on the screen, LOCATE allows you to specify where it appears. column and row must be numeric expressions.row is optional and if it is not included, LOCATE moves the cursor to the specified column position on the current line. You cannot use LOCATE to move the cursor backwards or up the screen: it will not move the cursor to a position on the same line which is to the left of the current position; and it will not move the cursor to a line above the current position. However, you can bypass this restriction if you place the HOME statement in front of LOCATE-see HOME. EXAMPLES 1 LOCATE 12, 5: ? "Hello" 2 c%=12: r%=1: LOCATE c%, r% 3 LOCATE 12: ? "Hello" NOTES The first example displays the word 'hello' at the 12th column on the sixth row. Example 3 displays 'hello' at the 12th column of whatever line the cursor happens to be on. 5-84 LOG PURPOSE Returns the natural logarithm (log to the base 'e') of a number. SYNTAX LOG (nexpr) COMMENTS The value of nexpr must be positive. Negative numbers or zero produce the error message 'Invalid numeric parameter'. EXAMPLES 1 numfieldc=LOG (numfielda) 2 numfieldc=LOG (datefielda-datefieldb) 3 numfieldc > LOG (numfielda * numfieldb) 4 x%=LOG (y%) 5 x%=LOG (y% * numfielda * (datefielda-datefieldb)) LOOKUP PURPOSE Detects whether an expression occurs in a file in a specified field. SYNTAX LOOKUP (expr, field.file) COMMENTS This function checks whether a given field in a file contains the expression in expr. That is, it answers the question: does expr exist in field.file? If it finds the expression, LOOKUP returns the value -1 (true); otherwise it returns the value 0 (false). expr and field.file must be of the same type (i.e. text or numeric). field.file must be the name of an indexed field in an open file. 5-85 LOOKUP plays a major role in cross-file validation and calculation. The file specified with LOOKUP can be any open file; so you can use this function to perform a relational lookup, where it checks whether the contents of a field in one file match the contents of a field in another file. If LOOKUP is successful-if it finds the expression you have specified-the record containing the expression becomes the current record, even though the file may not be the current file and is not displayed on screen. At the same time, the FOUND function is set to -1 to reflect the fact that the search has been successful. For a fuller explanation of LOOKUP, see Chapter 2, Volume 1. EXAMPLES 1 OPEN FILE "aaa": OPEN FILE "bbb" SELECT FIRST WHILE NOT EOF ("bbb") IF LOOKUP (field1.bbb, field1.aaa) THEN GOSUB Process_module WEND ? "Process completed": END Process_module: ...... ...... RETURN This example demonstrates a small program to process only those records in file 'bbb' that have a matching record in file 'aaa'. File 'bbb' could be an invoice file 'joined' to a customer file 'aaa' by a customer code (field1 in 'bbb' and 'aaa'). 5-86 LTRIM$ PURPOSE Trims leading spaces from a text expression or a text field. SYNTAX LTRIM$ (strexpr) COMMENTS LTRIM$ returns a string consisting of the original string specified by strexpr with any leading spaces removed. EXAMPLES 1 textfieldc=LTRIM$ (textfielda) 2 x$=LTRIM$ (textfieldc.filea) 3 ? LEN (x$); LEN (LTRIM$ (x$)) 5-87 MAKE PURPOSE Stores the file definition for a file it has been defined by CREATE and ADD. SYNTAX MAKE sbfname COMMENTS This command is used as the last step in the process of creating a new file. After the file has been defined by CREATE and ADD, MAKE writes the new file definition to disk together with any indexes that have created. Note that a file definition is not regarded as valid until the MAKE command has been executed. Before then, any error will have the effect of removing the file definition. EXAMPLES 1 CREATE "Address" ADD "Firstname; TXT REQ; 20; 1, 2" ADD "Lastname; TXT REQ IXD; 20; 1, 33" ADD "Street"; TXT; 25; 3, 2" (other field definitions) ........ ........ MAKE "Address" 5-88 MENU PURPOSE Sets up a user-define menu SYNTAX MENU column, item, state[, strexpr] COMMENTS Superbase let you define up to 10 menu each of which can have a maximum of 12 items. With the Menu command you supply the text for a single item and specify whether it will appear on the menu an enabled, disabled (ghosted) or with a check mark against it. Having defined a menu with a series of MENU commands-one for each menu item and one for the menu title-you can then use the MENU ON command to turn the menu (or menus) on. You also use MENU to specify a numeric variable which will return a value showing which item, if any, has been selected. column must be a numeric expression with a value in the range 1 to 10 giving the column number for the menu. To set up a menu in the first column on the left at the same position as the Superbase Project menu, you would enter a value of one. item must be a numeric expression with a value in the range 0 to 12, giving the number of the menu item. Item 0 is the menu heading, the text that appears on the menu bar. state can take a value of 0, 1 or 2.0 disables the item so that it appears on the menu as a ghosted option.1 enables it, 2 places a check mark against it. strexpr supplies the text for the item. For example, if you wished to define a menu in the first column which contained the option Deposits, you could enter: MENU 1, 3, 1, "Deposits" This would make Deposits the third item in the menu list. To disable the Deposits option, you would enter: MENU 1, 3, 0 Note that you do not need to specify the text a second time. 5-89 EXAMPLES 1 MENU 1, 0, 1, "Transactions" MENU 1, 1, 1, "Deposits" MENU 1, 2, 1, "Withdrawal" MENU 1, 3, 1, "Direct debit" MENU 1, 4, 1, "Standing orders" MENU 1, 5, 1, "Credit card" MENU ON a%, b% NOTES This example defines a menu in the first column with five options (items), all of them enabled. (The heading for the menu is Transactions.) MENU ON then turns the menu on. When the user selects an item, Superbase will place its column and item numbers in the variables a% and b%. MENU CLEAR PURPOSE Turns off all user-defined menus and clears their definitions from memory. SYNTAX MEMORY CLEAR COMMENTS If you want to define a new set of menus, you can use this command to clear any menus which have been defined previously. You may also use it when your menus are no longer required, in order to make the memory space they occupy available for other purposes. 5-90 MENU ON PURPOSE Turns user-defined menus on and specifies the variables which return the result of a menu selection. SYNTAX MENU ON nvar1, nvar2 COMMENTS MENU ON turns on any menus which have been defined with the MENU command and sets nvar1 and nvar2 to zero. When the user selects an item, Superbase places the column number in nvar1 and places the item number in nvar2. It also turns all the user-defined menus off. For example, if the second item in the third user-defined menu has been selected, the first numeric variable specified with MENU ON, will contain the value 3 and the second numeric variable will contain the value 2. You may sometimes want to turn the menus off without waiting for an item to be selected. You can do this with the command MENU OFF. EXAMPLES 1 menuloop: MENU ON a%, b% ON a% GOSUB sub1, sub2, sub3, sub4, sub5 GOTO menuloop NOTES This examples presumes that five menus have been defined and that sub1 to sub5 are subroutines which handle item selection for each menu. 5-91 MERGE PURPOSE Loads a text file and performs a mail merge. SYNTAX MERGE [TEXT filename] [WHERE conditions] COMMENTS This command merges the data in an '.sbf' file with a form letter in the Text Editor and outputs the results to the printer. It takes data from the current open file and-if WHERE is not included-prints one letter for each record in the file. You can use WHERE to set up a filter restricting the merge operation to only those records which match the conditions specified. The TEXT option lets you specify a text file on disk; Superbase will then load the file into the Text Editor before starting the merge operation. Although Merge is the program equivalent of the Mail Merge option on the Process menu, it does not allow you to preview letters on screen before printing them. To do this, use ? TEXT with the MERGE parameter. EXAMPLES 1 OPEN FILE "Address" MERGE TEXT "Mailshot1" WHERE Country LIKE "USA" MID$ PURPOSE Returns one or more characters from within a text string or text field. SYNTAX MID$ (strexpr, nexpr1[, nexpr2]) COMMENTS MID$ is more flexible than LEFT$ and RIGHT$ as it can extract characters from any point in a string.strexpr holds the string, and nexpr1 gives the starting point in the string.nexpr2 specifies the length of the substring to be extracted; if nexpr2 is not given, MID$ takes all the characters from the starting point to the end. 5-92 EXAMPLES 1 textfieldc=MID$ (textfielda, 10, 10) 2 textfieldc=LCASE$ (MID$ (textfielda, 8)) 3 MID$ (textfielda, 12, 1) LIKE[a-c] 4 x$-MID$ (textfieldc, 19, 2) 5 x$= (x$, 4) 6 ? MID$ (x$, 4, 2) 7 ASK;A$: I%=LEN (A$) FOR n%=I% TO 1 STEP -1 B$=B$ + MID$ (A$, n%, 1) NEXT ? B$ NOTES Example 7 inputs a word into A$ and turns it back to front. 5-93 MINS PURPOSE Extracts the number of minutes from a numeric value which holds the time in thousandths of a second. SYNTAX MINS (nexpr) COMMENTS Usually, nexpr will be a timefield or the result of a TIMEVAL calculation. EXAMPLES 1 mnts%=MINS (timefield) 2 ? MINS (NOW - start%); "minutes have elapsed" MOD PURPOSE Gives the remainder of a numeric expression after it has been divided. SYNTAX nexpr1 MOD nexpr2 COMMENTS nexpr1 is the number to be divided, nexpr2 is the number that divides into it (the divisor). MOD returns the remainder when nexpr1 has been divided by nexpr2. For example: 14 MOD 3 gives 2 as a result. It is equivalent to: 14-INT (14/3) * 3 EXAMPLES 1 ? (2.53 * 100) MOD 100 5-94 NOTES The example line strips off the integer part of a number and displays the first two figures after the decimal place. MODIFY PURPOSE Modifies a field definition. SYNTAX MODIFY field[, ] [field definition string] [, formula] [, formula] COMMENTS MODIFY is the program equivalent of the EDIT FILE option in PROJECT. It allows you to alter a field's parameters; for example, the field name or its length. The field definition and formula strings take the same form as they do with the ADD command. EXAMPLES 1 MODIFY Forename "Firstname; TXT REQ IXU; 15 U; 1, 12" 5-95 MONTH$ PURPOSE Takes a julian date number and returns the month of the year as a text string. SYNTAX MONTH$ (nexpr) COMMENTS The same limitations on which julian dates are acceptable apply to this function as they do to other date functions. The format of the text string is the full month name regardless of what current date format is - i.e., January, not Jan). Associated date functions are DAY DAYS DAY$ MONTH MONTH$ YEAR. EXAMPLES 1 textfieldc=MONTHS$ (datefielda) 2 textfieldc=MONTH$ (datefielda + 90) 3 textfieldc=MONTH$ (TODAY) 4 x$=MONTH$ (datefielda + VAL (textfielda)) 5 x$=MONTH$ (DAYS ("11 Jan 85") 6 ? MONTH$ (datefielda + 30) 5-97 NEW PURPOSE Clears the program area or text area. SYNTAX NEW [TEXT/QUERY/UPDATE] COMMENTS On its own, NEW erases any program that is currently in the computer's memory. When followed by TEXT, it clears the current text editor area of memory. Following it by or QUERY or UPDATE, clears their respective dialogs. Unlike the menu options Program New (and Text New) this command does not put you into the program (or text) editor. NEWLINE PURPOSE Sends a new line character (or characters) to an output device. SYNTAX NEWLINE[nexp] COMMENTS This command prints a new line at the current output device; i.e. with the screen display, it takes the cursor onto the start of the next line.nexp can be used to specify more than one new line. If nexp is not an integer, only the integer part will be taken. EXAMPLES 1 NEWLINE 2 2 FOR i%=1 to 20 ? i%: if i% MOD 5=0 THEN NEWLINE i%/5 NEXT i% 5-98 NOTES Example 2 outputs the numbers 1 to 5 with single line spacing, 6 to 10 with double spacing, and so on up to 20. NOW PURPOSE Gives the system time. SYNTAX NOW COMMENTS NOW shows the time of day in hours and minutes, using the current time format. If you have a real-time clock in your computer or you have set the system time, this will be the current time. Note that NOW actually holds the time in thousandths of a second. When you display the time, Superbase automatically translates it into hours and minutes. EXAMPLES 1 ? NOW 2 ? MINS (NOW) 3 timefield=NOW 5-99 NUMBASE PURPOSE Sets the numeric format in which numbers are displayed. SYNTAX NUMBASE string COMMENTS NUMBASE is the program equivalent of the Number Format option in the SET menu. string must be one of Superbase's valid numeric formats, as listed Chapter 2, Volume 1. For example, "z99999.00" or "z (+$, 000000.00". EXAMPLES 1 NUMBASE "z99999." Integer only format. 2 NUMBASE "+*****.00" Numbers displayed with a sign and leading cheque-protect. ON ERROR PURPOSE Tells DML to branch to another part of the program when an error occurs. SYNTAX ON ERROR [[GOTO] label] COMMENTS Normally, DML halts program execution and displays an error message when it detects an error. ON ERROR enables error trapping, and prevents the program from halting. Once an error has been detected, it causes the program to jump to the error handling routine specified with label. You can use ERRNO in your error handling routine to check on which error has occurred, and take appropriate action. In many cases, you will want the program to resume execution after detecting an error. You can do this with the RESUME statement. 5-100 To disable error trapping, use ON ERROR without a following label. EXAMPLES 1 ON ERROR GOTO check ...... ...... ...... check: IF ERRNO 11 THEN RESUME ELSE? "Are you sure you want to exit from this program?" ? "Press Y to exit, any another key to resume" WAIT a$ IF a$="Y" OR a$="y" THEN END ELSE RESUME ENDIF NOTES In this example, ON ERROR is used to check whether the Stop button has been clicked on or CTRL C has been pressed. Both these events generate error number 11, so the error handling routine (which starts at label 'check') first tests for this error number. If it finds that another error event has occurred, program execution is resumed at the line which caused the error. The error handling routine then asks if the user wishes to exit or not. Depending on the answer it receives, it either resumes execution at the line which caused the error (the line being executed when the user pressed STOP or CTRL C) or terminates the program. 5-101 ON GOSUB PURPOSE Calls one of a number of subroutines from a list of subroutines. SYNTAX ON nexp GOSUB label1 [, label2, label3, ...] COMMENTS This statement transfers program control to one of the subroutines given in the list. The value of nexp determines which subroutine the program jumps to. If nexp has valued at 1 the program branches to the subroutine at the first label, if nexp has a value of 2, it branches to the subroutine at the second label, and so on. Once the program has branched to a subroutine, it executes each statement in turn until it meets a RETURN statement. Then it jumps back to the line following the ON GOSUB statement. Any label can be repeated. If nexp is 0 or greater than the number of supplied labels, program control drops to the next statement after the ON GOSUB statement. EXAMPLES 1 ON x% GOSUB lab1, lab2, lab3 2 ON x% GOSUB lab1, lab2, lab1, lab2, lab1 ON GOTO PURPOSE Branches to one of a list of labels. SYNTAX ON nexp GOTO label [, label, ....] COMMENTS This command transfers program control to one of the program lines given in the list. The value of nexp determines which label the program jumps to. 5-102 If nexp has valued at 1 the program branches to the first label, if nexp has a value of 2, it branches to the second label, and so on. For a general description of GOTO refer to GOTO itself. nexp should be a positive integer. If it is not an integer, the whole number part will be taken. Any label can be repeated. If nexp is 0 or greater than the number of supplied labels, program control drops to the next statement after ON GOTO. EXAMPLES 1 ON x% GOTO lab1, lab2, lab3 2 ON x% GOTO lab1, lab2, lab1, lab2, lab1 ? "Reached here only when x% is 0 or greater than 5 OPEN PURPOSE Opens a text file on disk or Comms channel for input/output. SYNTAX OPEN filename FOR[INPUT/OUTPUT/APPEND] COMMENTS When used for output to a file, OPEN has the same effect as OUTPUT TO file. There is only one channel for INPUT, and one for OUTPUT, so you cannot have two output channels, or two input channels. However, you can have one of each open at the same time. APPEND is an output channel and specifies that file is to be appended to. You must not try to specify OPEN "aaa" FOR OUTPUT APPEND. If using OPEN, file is overwritten without warning. If using APPEND, file need not exist. If using INPUT, file must exist. 5-103 EXAMPLES 1 OPEN "aaa" FOR OUTPUT 2 OPEN "aaa" FOR APPEND 3 OPEN "bbb" FOR INPUT: OPEN "aaa" APPEND lab1: INPUT LINE a$: ? a$ IF NOT EOF ("*") THEN GOTO lab1 CLOSE INPUT: CLOSE OUTPUT NOTES Example 3 appends the contents of file 'bbb' to file 'aaa'. Notice that the last line of the program CLOSEs the files that OPEN has opened. This practise is strongly recommended: you should always close a file when you have finished writing to it. OPEN FORM PURPOSE Loads a form from disk and displays it in the database window. SYNTAX OPEN FORM form COMMENTS form must be a string expression giving the file name of a form. Superbase will also open any database files associated with the form. EXAMPLES 1 OPEN FORM "Invoice" OPEN FIELDS PURPOSE Specifies which fields are displayed. SYNTAX OPEN FIELDS [FILE sbfname] fieldlist 5-104 COMMENTS This command is the program equivalent of the Open Fields option on the Project Menu. fieldlist consists of the list of fields required to be open. To remove any restrictions on which fields are shown, use the CLOSE FIELDS command. EXAMPLES 1 OPEN FIELDS FILE "Address" Firstname, Lastname, Country, City OPEN FILE PURPOSE Opens a database file and its default index. SYNTAX OPEN FILE sbfname [; password] COMMENTS Note the distinction between OPEN FILE "aaa" which opens a database file, and OPEN "aaa" (FOR INPUT) which opens a text file. sbfname is compulsory, and if a password is required to access the file, then it is also compulsory (use a semicolon to separate the filename from the password). INDEX followed by a field name may be added to the end of an OPEN FILE command, allowing you to select an index other than the default index. But it can only be used if the file has already been opened by a direct command or an earlier program line. As explained in the entry for the INDEX command, DML parses the whole line before executing it; so if you refer to a field, it must be a field in a file that already been opened. Otherwise an error will result. EXAMPLES 1 OPEN FILE "aaa" 2 x$="bbb": OPEN FILE x$ 5-105 3 OPEN FILE "aaa"; "John" NOTES In example 3, 'John' is the password for the file 'aaa'. ORDER PURPOSE Sets the order for Query output. SYNTAX ORDER [&nexpr]field[ASCENDING/DESCENDING] [, field] ASCENDING/DESCENDING] [, .....] COMMENTS ORDER is a Query Language command and can only be entered in a query section - i.e., it works in conjunction with the Query Language command Select. This command is the program equivalent of the Order command line in the query definition dialog: it takes the same syntax and serves the same purpose. The field specified with the ORDER command determines the order in which the field in the SELECT line are output. If you are familiar with the concept of sorting, you can think of ORDER as setting the sort 'key' for query output. field must be a field in an open file, but it does not need to be an indexed field; nor does it have to be one of the fields in the SELECT line. ASCENDING and DESCENDING allow you to specify whether data is sorted in ascending or descending order. If you specify a text field with the ORDER command - i.e., if you specify it as the sort key-Superbase outputs record data according to the alphabetical order of the sort field. DESCENDING reverses the order and sorts the field from Z to A. With numeric, date and time fields, ASCENDING sorts data in numeric, date or time order; and DESCENDING reverses the order. By default, fields are sorted in ascending order; so it not strictly necessary to include the ASCENDING parameter. You can also specify more than one field in the ORDER line, separating each with a comma. If enter two fields, the first field takes precedence as a sort key 5-106 over the second field; i.e., records are first sorted according to the first field, and then any duplicate data items are sorted according to the second key. The same applies if there are more than two fields: the second key has priority over the third, the third has over the fourth, and so on. EXAMPLES The examples illustrate how ORDER works by taking a limited set of records and showing some of the different ways in which they may be sorted. Each record contains data from three fields, Firstname, Lastname, and Country. 1 SELECT Firstname, Lastname, Country ORDER Lastname This examples takes Lastname as the sort key and produces the following output: FIRSTNAME LASTNAME COUNTRY Pierre Arnauld France William Carter USA Gerde Hemrich West Germany John Miles England Anne Richardson USA Peter Smith England Robert Brown England 2 SELECT Firstname, Lastname, Country ORDER Country The output from this query would be as follows: FIRSTNAME LASTNAME COUNTRY Robert Brown England John Miles England Peter Smith England Pierre Arnauld France William Carter USA Anne Richardson USA Gerde Hemrich West Germany 5-107 3 SELECT Firstname, Lastname ORDER Country DESCENDING, Lastname ASCENDING The output from this query is: FIRSTNAME LASTNAME Gerde Hemrich William Carter Anne Richardson Pierre Arnauld Robert Brown John Miles Peter Smith 4 SELECT Firstname, Lastname, Country ORDER Country, Firstname This example uses Country as the primary sort key and Firstname as the secondary key to produce the following output: FIRSTNAME LASTNAME COUNTRY John Miles England Peter Smith England Robert Brown England Pierre Arnauld France Anne Richardson USA William Carter USA Gerde Hemrich West Germany NOTES The default length for sorting is 15 characters per field. Superbase gives equal weighing to upper case, lower case and accented instances of characters. The & character followed by a value up to the length of the field may precede any field, specifying the number of characters that will be used in sorting. 5-108 OUTPUT TO PURPOSE Opens a text file on disk for output. SYNTAX OUTPUT TO filename COMMENTS This command makes the disk the current output device and sends any future output to filename. It has the same effect as OPEN filename FOR OUTPUT. If the text file already exists on disk, any output command issued after OUTPUT TO, will overwrite the file. If you want to add data to an existing text file, use OPEN filename FOR APPEND. EXAMPLES 1 OUTPUT TO "Names" ? Lastname CLOSE OUTPUT 2 OUTPUT TO a$ NOTES Example 1 stores the contents of the Lastname field (in the current record) on disk in the text file Names. 5-109 PASSWORD PURPOSE Sets new password (or none) for a specified file. SYNTAX PASSWORD sbfname [; passwords] COMMENTS sbfname must be an open file and, as usual with filenames, must be included in quotation marks. If no password is given, the existing password for the specified file is removed. EXAMPLES 1 OPEN FILE "aaa; John" PASSWORD "aaa" Removes passwords. 2 PASSWORD "aaa; Rosebud" Sets a password for the file 'aaa'. 3 OPEN FILE "aaa; John" PASSWORD "aaa; John; Paul; George" Adds passwords for read/write and read only access privileges. PCOL PURPOSE Return the column position of the print head on the current output printer or resets the print head's position. SYNTAX PCOL (nexpr) COMMENTS If nexpr is zero, the function returns the column position of the print head 5-110 on the current printer. For the Row position, see PROW. See also LOCATE. You can also use this function to set the counter Superbase uses to keep track of the print head's position. Giving nexpr a positive value, sets the counter to that value. The print head itself is not moved. This feature is used to reset the internal count after issuing a series of printer commands which have not in fact moved the print head, for example, after switching to high density graphics mode. EXAMPLES 1 x%=PCOL (0) 2 ? PCOL (0) POSITION PURPOSE Sets the data pointer to a new position in an ASCII file. SYNTAX POSITION nexp COMMENTS When you read data from an ASCII file on disk, Superbase uses an internal pointer to keep track of it. The OPEN file FOR INPUT command sets the pointer to zero, the position of the first character in the file. Thereafter it is incremented by one for each character that is input using the INPUT command. POSITION sets the pointer to the character position specified by nexpr. Normally, the data in an ASCII file is read into the computer sequentially. With Position, you can input character data on a more selective basis. You will only be able to take advantage of this command if you know where the data is stored in a file. Superbase stores data in variable length fields (as opposed to fixed length fields): when you create an ASCII file from an '.sbf' file by exporting it, the amount of space occupied on disk by field data may vary from record to record. This means that there is no simple way of knowing the position of any particular field or record. One solution to this problem is to create an ASCII file from a database file using the query option Output to Disk. When you do this, Superbase stores 5-111 the data in fixed length fields-each field takes the length set in the file definition. You can then work out the number of characters occupied by a record in the ASCII and use this figure to retrieve specific records or fields. For example, if the record length was 49 characters, you would enter: POSITION 49 * 5 + 1: INPUT LINE a$ to retrieve the fifth record in the file (you need to add one because the first character position is zero). EXAMPLES 1 OPEN "Cust.asc" FOR INPUT FOR n%=0 to 76 * 12 STEP 77 POSITION n% INPUT & 15, a$ ? a$ NEXT CLOSE INPUT NOTES This example inputs the first field from the first twelve records in the ASCII file Cust.asc. It assumes that the record length is 77 characters and that the length of the first field is 15 characters. PRINT PURPOSE Sends information to the printer. SYNTAX PRINT[expressionlist] COMMENT PRINT, followed by a semicolon and nothing else, selects the printer as the current output device. The ? command can then be used to send information to the printer. You can also use PRINT to output information directly to the printer, by following the command with one or more expressions. But note that any use of PRINT makes the printer the current output device. The items in the expression list following the Print command may be separated by a semicolon or a comma. If a semicolon is used, Superbase will print the 5-112 expressions one after the another without any spaces in between; a comma has the effect of inserting a space between items. In some circumstances, you may also dispense with separators altogether. Thus, provided it can distinguish between different items, Superbase will accept a list of expressions which are entered on the line head to tail; for example: PRINT a$b$c%"Hello" EXAMPLES 1 PRINT; ? MEMORY DISPLAY; 2 PRINT BF "The items in the following list will be printed in bold face" PRINT "One", "Two", "Three"; CHR$ (12) DISPLAY; NOTES The first example prints the current program's variables and their contents. Example 2 prints a list of items, and then sends the form feed character - CHR$ (12) -to the printer. Both examples use the DISPLAY command to make the screen the current output device after the print operation is finished. 5-113 PROTECT PURPOSE Saves the current program in an encrypted form. SYNTAX PROTECT filename COMMENTS Use this command to ensure that program files are not seen by anyone else. It stores a file on disk in an encrypted (scrambled) form so that it can be run but not edited. If the first line of a program is a REM statement, PROTECT displays that line, but hides the rest of the program from any attempt to edit or LIST it. EXAMPLES 1 PROTECT "myprog" PROW PURPOSE Returns the row position of the print head on the current output printer. SYNTAX PROW (nexpr) COMMENTS If nexpr is zero, the function returns the row position of the print head on the current printer. For the Column position, see PCOL. See also LOCATE. Giving nexpr a positive value resets Superbase's internal row counter. See PCOL. EXAMPLES 1 x%=PROW (0) 2 ? PROW (0) 5-114 QUIT PURPOSE Exits from Superbase. SYNTAX QUIT COMMENTS This has same effect as selecting the Quit option from the Project menu. It exits from Superbase and returns the user to the desktop interface. 5-115 READ PURPOSE Reads the data given in a DATA statement and assigns it to a variable or field. SYNTAX READ var/field[, var/field] [, .....] COMMENT The types of variables or fields used with a READ command must match the types of data expected-numeric variables or numeric fields for numeric data and string variables or string fields for string data. DML uses a pointer to keep track of where it is in the list of DATA items; that is, each time a data item is read, DML moves the pointer on to the next item in the list. If you wish to read the same data again, you can place a label in front of a DATA statement and use RESTORE. EXAMPLES 1 READ a%, b$, fielda.filea, fielda.fileb RECCOUNT PURPOSE Counts the number of records in a file. SYNTAX RECCOUNT (sbfname) COMMENTS This function returns a number showing how many records there are in the file specified. You can use the empty string as an argument-RECCOUNT ("") -to refer to the current file. EXAMPLES 1 ? RECCOUNT ("Orders") 5-116 2 x%=RECCOUNT (x$) 3 OPEN FILE ("address") SELECT FIRST FOR n%=1 to RECCOUNT ("address") VIEW SELECT NEXT NEXT n% NOTES Example 3 displays all the records in the file "address" in turn. REM PURPOSE Inserts a non-executable comment (a remark) into a program. SYNTAX REM[text] COMMENTS REM has the effect of cancelling any statements after it. This makes it useful when you are testing a program-placing it at the start of a multi-statement line puts the following statements temporarily out of action. More generally, use REM to annotate a program in order to explain how it works or what it does. A single quotation mark after a command without an intervening colon also acts as a REM statement. EXAMPLES 1 REM this is a remark 2 ....: FILE "aaa" open aaa 3 ....: FILE "aaa": REM open aaa 4 FILE "aaa": REM eliminate next commands: INDEX abc: SELECT FIRST 5-117 NOTES Examples 2 and 3 have identical effects and demonstrate the two different ways of entering a comment. In example 4, the REM statement means that the INDEX and SELECT FIRST commands are not executed. REMOVE FILE PURPOSE Removes a database file from disk, along with its associated definition and index files. SYNTAX REMOVE sbfname COMMENT This command operates in the same way as the Remove File menu option. Note that you are not asked for confirmation-the file is just removed. EXAMPLES 1 REMOVE FILE "aaa" 2 REMOVE FILE "DF1: aaa" 3 REMOVE FILE "GEM\SBASE\aaa" REMOVE FROM PURPOSE Removes records which match the conditions specified. SYNTAX REMOVE FROM FILE sbfname[WHERE conditions] COMMENTS This command works in the same way as the equivalent PROJECT-REMOVE-FILE menu option. It deletes records from a file on disk. 5-118 FILE sbfname has to be open, and if the file requires a password, you must have full access to it. WHERE conditions is optional and is set up in the same way as a filter. If it is not included, the command acts on all the records in a file. EXAMPLES 1 REMOVE FROM FILE "aaa" WHERE Lastname LIKE "[a-c]*" 2 REMOVE FROM FILE "aaa" This empties the file of all its data. REMOVE INDEX PURPOSE Removes an index on the current file from disk. SYNTAX REMOVE INDEX index COMMENT This command works in the same way as the Remove Index option on the Project menu. The file must be open, and, if it requires a password, you must have full access privileges. index is the name of an indexed field. It can be entered with a file extension. EXAMPLES 1 REMOVE fielda 2 REMOVE fieldb.aaa 5-119 RENAME PURPOSE Renames a file on disk. SYNTAX RENAME old.filename[, /TO]new.filename COMMENT This command works in the same way as the RENAME command in MS DOS or Amiga DOS, but allows you to rename a file without exiting from Superbase. You have the option of using either a comma or the keyword TO as the separator between the two file names. EXAMPLES 1 RENAME "aaa", "bbb" 2 RENAME "aaa" TO "bbb" REORGANIZE PURPOSE Reorganizes the current file or a specified file. SYNTAX REORGANIZE [FILE sbfname] [TO]sbfnameb COMMENTS This command is the program equivalent of the Reorganize option on the System menu (see Chapter 8, Volume 1). It takes a file on disk, reorganizes it, and stores it as sbfnameb. IF the FILE option is not used, the current file is reorganized. sbfnameb can include the pathname for another directory or disk. If you enter a pathname without a file name following it, the file will be reorganized under the same name. 5-120 Note that you cannot reorganize a file under the same name in the same directory; i.e. if a pathname is not supplied, sbfnameb must not be the same as sbfnamea. EXAMPLES 1 FILE "aaa": REORGANIZE TO "copy" 2 REORGANIZE "aaa" TO "a: mydir\" 3 REORGANIZE "aaa" TO "DF1: Mydir/" Example 1 creates a reorganized file 'copy' in the current directory, Example 2 creates a reorganized file 'aaa' in directory mydir on the disk in drive a (or, in example 3, drive DF1 on the Amiga). REPLICATE PURPOSE Replicates a character a given number of times. SYNTAX REPLICATE (strexp, nexp) COMMENTS REPLICATE repeats the character in strexpr the number of times given in nexp. EXAMPLES 1 textfieldc=REPLICATE ("*", 10) 2 x$=REPLICATE (textfieldc, 4) 3 x$=REPLICATE (MID$ (x$, 4, 2), 6) 4 x$=REPLICATE (" ", 25) NOTES Example 4 fills x$ with 25 spaces, but see function SPACE$. 5-121 REPORT PURPOSE Specifies the field or fields on which totals (and other report statistics) will be produced for the report as a whole. SYNTAX REPORT [SUMMARIZE] [params]fieldname[, fieldname][, ....] COMMENT Report has two uses. When you create a Report with the Forms Editor, Superbase generates a Report statement by noting the fields which have been specified in an AFTER REPORT section; i.e., if the AFTER REPORT section in a Report program contains the statements: ? SUM amount ? COUNT deposits Superbase will generate the following line in the program: REPORT amount, deposits If you are writing a Report program yourself (as opposed to modifying a program generated by Superbase), you should remember to enter a Report statement including the names of any fields for which you wish totals and other report statistics to appear. The second application for REPORT is a query language command. In this context, it allows you to create a program line which is equivalent to the REPORT command line in the query definition dialog. REPORT is used here to specify the fields for which totals and other statistical. When you use the SUMMARIZE option, Superbase suppresses the main detail of the report and prints just the summary information. 5-122 REQUEST PURPOSE Displays a Superbase dialog. SYNTAX REQUEST text1, text2, type[, nvar[, strvar[, len]]] COMMENT REQUEST allows you to select one of Superbase's dialogs and display it on screen. To some extent you can also customize a dialog to your own requirements. Thus you can place a title in box, and you can specify the text string that initially appears in the dialog's command line or selection box. For certain dialogs, it also possible to specify the length of the box. text 1 and text2 are the first and second line of the dialog title. They must be included although they can be "". The maximum length for each line is 50 characters. type is the dialog type. It defines the type of dialog according to the table shown below. nvar is a numeric variable. It returns a value of 1 if OK is selected and there is an entry into the string dialog. If CANCEL is selected or there is no entry into the string dialog, it returns 0. strvar can be used with dialogs which have a string entry box and has two functions: It is used to place a default value into the string box, i.e., the text string in strvar is entered into the string box when the dialog is displayed. It returns the string which the user enters in the box. len specifies the length of the string box (where appropriate). This is particularly useful for the information dialogs. For dialog types 5-16 it is not need as Superbase sets the box to the maximum file name length of 50 characters. For dialog type 4 len must be specified. 5-123 Types of Dialogs available: TYPE DIALOG BUTTONS 0 string OK 1 string OK CANCEL 2 string OK 3 string OK CANCEL 4 string OK CLEAR CANCEL 5 Database Files OK CLEAR CANCEL 6 Open Fields List OK CLEAR CANCEL 7 Indexed fields OK CLEAR CANCEL 8 Non indexed fields OK CLEAR CANCEL 9 Field Info OK CLEAR CANCEL 10 Open Database Files OK CLEAR CANCEL 11 Database Files OK CLEAR CANCEL 12 Program Files OK CLEAR CANCEL 13 Text Files OK CLEAR CANCEL 14 Query Files OK CLEAR CANCEL 15 Update Files OK CLEAR CANCEL 16 Function Key Files OK CLEAR CANCEL 17 Directory listing OK CLEAR CANCEL 18 Subdirectory List OK CLEAR CANCEL Note that a database file must be open before dialogs 5 to 9 can be selected. EXAMPLES 1 REQUEST "", "", 2, 2 a%=0:a$="":REM initialize nvar and strvar REQUEST "Select a program", "", 5, a%, a$, 32 IF a%=0 THEN ? "OK so you do not want to run a program":END CHAIN a$ 3 a%=0:a$=":OPEN FILE "aaa" REQUEST "Select an Index", "", 7, a%, a$ IF a%=0 THEN END INDEX a$ lablook:a$="":REQUEST "Enter a key", "", 1, a%, a$, 15 IF a%=0 THEN END SELECT KEY a$ IF FOUND ("aaa") THEN GOTO lablook2 REQUEST "No Record with key" + a$, "See Nearest ?", 1a% 5-124 IF a%=0 THEN GOTO lablook lablook2:VIEW:WAIT FOR 5:GOTO lablook NOTES Example 1 puts an "OK" dialog up for 2 seconds. Example 2 allows the user to select a program to run. Example 3 in essence duplicates the key lookup function from the ? button on the Superbase Control Panel. Note the concatenation of the first title line of the dialog on the third from last line. RESTORE PURPOSE Moves the data pointer back to the first DATA statement, or to a specified label. SYNTAX RESTORE[label] COMMENTS The data pointer is the internal pointer that Superbase uses to keep track of which DATA statements have been read. Initially it has a value of zero and points to the first DATA statement. As you READ data into variables or fields, the data pointer is increased by one for every data item read. This command resets the data pointer. If you do not specify label, the data pointer is reset to the beginning of the first DATA statement. If you specify label, the data pointer is reset to the data statement following the label. EXAMPLES 1 RESTORE 2 RESTORE datalabel1 5-125 RESUME PURPOSE Resumes execution after an error. SYNTAX RESUME[NEXT/label] COMMENTS The RESUME command works in conjunction with the ON ERROR GOTO command which is used to trap program errors. RESUME, on its own, returns program control to the statement that caused the error. When NEXT is included, the statement returns program control to the statement after the one which caused the error. label transfers program control to the label specified. EXAMPLES 1 REM Top of program ON ERROR GOTO errlab1 SELECT FIRST:? fieldname ..... ..... errlab1: IF ERRNO=44 THEN OPEN FILE "aaa": REM file not open RESUME 2 REM read data ON ERROR GOTO errlab2 ..... READ x$ ..... endread:..... ..... ..... errlab2: IF ERRNO=18 THEN RESUME endread: REM out of data 5-126 RETURN PURPOSE Returns from a subroutine. SYNTAX RETURN COMMENTS The RETURN command is used to mark the end of a subroutine. It instructs the computer to transfer program control to the statement immediately following the GOSUB or ON GOSUB statement which initially called the subroutine. See GOSUB. RIGHT$ PURPOSE Extracts one or more characters from a text string or text field, starting from the right-hand end of the string. SYNTAX RIGHT$ (strexpr, nexpr) COMMENTS This function starts at the right of a string given in strexpr and extracts nexpr characters. EXAMPLES 1 textfieldc=RIGHT$ (textfielda, 10) 2 textfieldc=LCASE$ (RIGHT$ (textfielda, 1)) 3 IF RIGHT$ (textfielda, 1) ="s" THEN.... 4 x$=RIGHT$ ("ABCD", 2) 5 x$=RIGHT$ (x$, 4) 5-127 RND PURPOSE Returns a random number. SYNTAX RND (nexpr) COMMENTS What the function returns depends on the value of nexpr. It nexpr is less than zero, the random number generator is reseeded. This means that a new series of random numbers will be generated, completely unrelated to the last series. It also allows you to generate the same series again for testing purposes, by entering the same seed. If nexpr is zero, the number returned is the same as the previous one. If nexpr is positive, a new random number is generated. The random number returned is in the range 0 to 1. Technically, it is never zero and never unity, but all values between 0 and one will be randomly generated and the distribution of numbers will be relatively flat. EXAMPLES 1 numfieldc=RND (numfielda) 2 numfieldc=RND (2) * 12 3 textfieldc=MID$ (x$, RND (2) * 6 + 1, RND (2) * 12 + 1) 4 $x=RND ($y) 5 ? RND ($x) 5-128 ROW PURPOSE Returns the row position of the cursor on the screen. SYNTAX ROW (0) COMMENTS This function shows how far down the screen the cursor is. For the column position, see COL. EXAMPLES 1 x%=ROW (0) 2 ? ROW (0) NOTES In practise, example 2 would be pointless, because it changes the position of the cursor in the course of printing it. RUN PURPOSE Executes a program from memory, or loads it from disk and then runs it. SYNTAX RUN[filename] COMMENTS This will run the program currently in memory when used as a command or as a program statement without the filename option. If filename is used to specify a program, Superbase loads the program from disk and then runs it. If filename is specified, it must be a string variable or a string constant in quotation marks. 5-129 SAVE PURPOSE Saves any of the following types of file: program, text, function key, query, and update files. SYNTAX SAVE [TEXT/KEY/QUERY/UPDATE]filename[, TEXT] COMMENTS filename is required. Superbase detects files of different types as follows: aaa.sbk is a saved function key set aaa.sbp is a saved program aaa.sbq is a saved query aaa.sbt is a saved document (text) aaa.sbu is a saved update If none of the options TEXT, KEY, QUERY or UPDATE is used, Superbase assumes that filename refers to an '.sbp' file and attempt to save a program file. If you include TEXT as the last parameter, DML saves a program file as a text file. Only one of the options, TEXT, KEY, QUERY or UPDATE, can be used at a time. SAVE FILE PURPOSE Saves the current file definition. SYNTAX SAVE FILE sbfname COMMENTS When you create a new file, you can use MAKE to save the file definition. SAVE FILE, however, must be used after you have edited a file definition with MODIFY. 5-130 SAY PURPOSE Converts a text string into speech, using the Amiga's speech synthesis facility. SYNTAX SAY [USING parameters]exprlist COMMENTS SAY is only available on the Amiga. The parameters for the USING option are in this order: Pitch, Inflection, Rate (wpm), Sex, Phonemic The following table gives the range of each parameter and its default value: PARAMETER RANGE DEFAULT NOTES Pitch 65-320 110 Inflection 0-1 0 0 is expressive, 1 is monotone Rate in wpm 40-400 150 Sex 0-10 0 0 is male, 1 is female Phonemic 0-1 0 0 translates to phonemes, 1 assumes phonemes SAY only works with string expressions. To hear an external sound field use the SHOW command. EXAMPLES 1 OPEN FILE "Address" SELECT FIRST WHILE NOT EOF ("Address") SAY Forename; Lastname; "comes from"; City; Country SELECT NEXT WEND 2 SAY USING 280, 1, 200, 1, 0"Hello there", USING 140, 1, 200, 0, 0 "Well, hello there" 5-131 SCRDUMP PURPOSE Outputs a copy of the screen to the printer-carries out a screen dump. SYNTAX SCRDUMP COMMENTS Only available on the Amiga. SECS PURPOSE Returns the numbers of seconds from a time field. SYNTAX SECS (nexpr) COMMENTS nexpr will usually contain a time in milliseconds (thousandths of a second) from a time field or from the result of a TIMEVAL calculation. EXAMPLES 1 scnds%=SECS (timefield) 2 scnds%=SECS (TIMEVAL ("10:22AM")) SELECT COMMANDS The following SELECT commands relate to Record selection: SELECT CURRENT SELECT DUPLICATE SELECT FIRST SELECT KEY SELECT LAST SELECT NEXT 5-132 SELECT PREVIOUS SELECT REMOVE SELECT WHERE SELECT commands can only be used on an open database file, although this does not have to be the current file. These commands do not display records on screen. To do this, you need to use VIEW. Similarly, although they can be used with any open file, the SELECT commands do not automatically make an open file the current file. For example, SELECT LAST selects the last record in a file (on index) even if the file is not current. If the file is current, executing the VIEW command will be enough to display the last record. But with any other open file, you will also need to use the FILE command (as opposed to the FILE parameter) before you can display the record (FILE makes an open file the current file). SELECT CURRENT PURPOSE Selects the current record. SYNTAX SELECT CURRENT [FILE sbfname] [INDEX index] COMMENT This command has the same effect as the Current Record button on the Control Panel at the bottom of the screen. Use INDEX to select the current record using a different index. 5-133 SELECT DUPLICATE PURPOSE Selects the next record with the same key. SYNTAX SELECT DUPLICATE [INDEX index] COMMENTS This command finds the next record with the same key as the current key. The key is the field on which the file is currently indexed. If SELECT DUPLICATE fails to find a record with the same key, the EOF function is set to true. 1 SELECT FIRST:VIEW:x%=1 lab1:SELECT DUPLICATE IF NOT EOF ("aaa") THEN VIEW:x%=x% + 1:GOTO lab1 ? "End of "; x%; "duplicates-strike key" lab2:SELECT PREVIOUS:SELECT NEXT:IF EOF ("aaa") THEN END CLS:VIEW:GOTO lab1 Example 1 displays duplicate entries on an index. In Table view, it shows a set of records at a time. SELECT FIRST PURPOSE Selects the first record in the current or specified index sequence. SYNTAX SELECT[FILE sbfname] [INDEX index] COMMENTS This command has the same effect as the First Record button on the Control Panel. Use the INDEX parameter to alter the current index. EXAMPLES 1 SELECT FIRST 5-134 2 SELECT FIRST "Stock" INDEX Prodcode NOTES Example 1 selects the first record in the current file according to the current index. Example 2 selects the first record in the Stock file according to the Prodcode index. SELECT KEY PURPOSE Selects the first record with matching string. SYNTAX SELECT KEY string [FILE sbfname] [INDEX index] COMMENTS This command has the same effect as the Key Lookup button on the Control Panel. It searches the file for the first record whose index field matches the string specified. Unlike the other SELECT commands, this command does not affect the EOF function, but instead sets the FOUND Function (see the example given for FOUND). EXAMPLES 1 SELECT KEY "Zollinger" VIEW 2 SELECT KEY "Johnson" FILE "Customer" INDEX Lastname IF FOUND ("Customer") THEN FILE "Customer":VIEW NOTES Example 1 selects the record in the current file whose Lastname field contains the name Zollinger. It assumes that the current file is indexed on Lastname. The program in example 2 selects the record in the Customer file whose Lastname field contains the name Johnson. The Customer file must have already been opened, but it does not have to be the current file. If the program finds a record with a matching key, it uses the FILE command to make the Customer file current and then displays the record. 5-135 SELECT LAST PURPOSE Selects the last record in the current or specified index sequence. SYNTAX SELECT [FILE sbfname] [INDEX index] COMMENTS Has the same effect as the Last Record button on the Control Panel. SELECT NEXT PURPOSE Selects the next record in the current or specified index sequence. SYNTAX SELECT NEXT [FILE sbfname] [INDEX index] COMMENT Has the same effect as the Next Record button on the Control Panel. SELECT PREVIOUS PURPOSE Selects the previous record in the current or specified index sequence. SYNTAX [FILE sbfname] [INDEX index] COMMENTS This command has the same effect as the Previous button on the Control Panel. 5-136 SELECT REMOVE PURPOSE Removes the current record in the current file or another open file. SYNTAX SELECT [FILE sbfname] COMMENTS This command has the same effect as the Remove option on the Record menu. SELECT WHERE PURPOSE Selects first record that matches the filter conditions or removes the filter. SYNTAX SELECT WHERE[[FILE sbfname] [conditions]] COMMENTS This command has the same effect as the Filter button on the Control Panel. conditions is set up in the same way as the string gadget in the Filter requestor. If not specified, the current filter conditions are cleared. SELECT WHERE can only be used to set a single file filter. If you enter the name of a field which also occurs in another open file, you should include the file the file name as an extension. Otherwise, Superbase may assume you are attempting to use this command in a multi-file operation, and will issue the error message: Can't do this This Where statement must be single file If you wish to set a multi-file filter - to select records whose field data matches the data in another file - use the LOOKUP function or the query language command WHERE. 5-137 EXAMPLE 1 SELECT WHERE fielda LIKE "[a-c]" 2 SELECT WHERE "Stock" fieldb LIKE "[a-c]" 3 SELECT WHERE NOTES Once set, the Control Panel filters remains active until it is cleared. Example 3 clears the filter which may have been set by a previous SELECT WHERE command or by direct entry in the filter dialog. QUERY LANGUAGE COMMANDS DML's Query Language commands allow you to create a program which duplicates a Superbase query. These commands are used in report programs created by the Forms Editor; but they can also be used for any of the query applications described in Chapter 11, Volume 1 - sorting records, creating complex multi-file filters, merging files, and so on. In Superbase a query is defined by the four command lines in the query definition dialog (Chapter 5, Volume 1, introduces this dialog; Chapter 11 explains it in more detail). These command lines can be reproduced in a program by using the query language commands. SELECT is used to define the Fields command line; REPORT defines the Report command line; WHERE corresponds to the FILTER line; and ORDER is used for the Order line. You will find a explanation of these commands under their respective keyword entries. Here, we will describe how they work together to form a query section. A query section must start with the SELECT command and it should end with END SELECT. Any other query language commands are optional; you will include them according to your requirements. Thus, if you wish to use a filter, you will include a WHERE command within the query section. 5-138 Likewise, if you wish to use reporting functions such as SUM and COUNT, you will need to insert a REPORT command after the SELECT command and before END SELECT. You can think of REPORT, WHERE and ORDER as modifying the query output which is specified with the SELECT command. When SELECT is used on its own - to form a single line query section - it outputs data from each record in a specified file (or files) in turn. For example: SELECT Lastname.Address, Country.Address: END SELECT This outputs a line showing the contents of the fields Lastname and Country for each of the records in the Address file. As such, SELECT works in the same way as the ? command except that it acts on all the records in a file. If you inserted TO PRINTER after Country.Address in the example above, SELECT would output data to the printer. The TO device parameter provides an equivalent to the Output options in the query definition dialog. You can use to specify an output device other than the screen:the printer, an ASCII file, or a new '.sbf' file. A QUERY LANGUAGE EXAMPLE Any query created with the query definition dialog can be reproduced under program control. We can illustrate this by converting a query file (an '.sbq' file) into a program, taking the Deptran file supplied with the demonstration file disk as an example. Deptran can be displayed on screen using the LIST option from the System menu. It looks like this: SB CLIENTS DEPOSITS Deposit Transaction Report ON "Clients" Firstname.Clients, Lastname.Clients, ON "Deposits" @24 Bank, Amount, Deposits REPORT SUM Amount COUNT GROUP Lastname.Clients SUM Amount Lastname.Clients=Lastname.Deposits Lastname.Clients SB on the first line indicates that this is a Superbase Professional query file as opposed to one created in Superbase Personal. The next two lines contain the names of the database files which are associated with this query. 'Deposit 5-139 Transaction Report' is the query title, and the remaining lines represent the four query command lines. Before we can load this file into the Program Editor, we need to change its name to Deptran.sbp. To do this, select COPY from the System menu and, after selecting Deptran.sbq as the file to be copied, enter then name Deptran.sbp. You can now load the file into the Program Editor using the Program open option. Converting it to a program is just a matter of deleting two lines and inserting keywords in the others. Once you have made these changes, the program should look like this: OPEN FILES "CLIENTS" OPEN FILE "DEPOSITS" SELECT ON "Clients" Firstname.Clients, Lastname.Clients, ON "Deposits" @24 Bank, Amount, Deposits REPORT SUM Amount COUNT GROUP Lastname.Clients SUM Amount WHERE Lastname.Clients=Lastname.Deposits ORDER Lastname.Clients Note that the report line is the same as in the query file and does not need to be altered. If you now run this program, it will have the same effect as running Deptran from the query definition dialog (by clicking on OK). SELECT PURPOSE Specifies which are to be output from a query. SYNTAX SELECT [params]fields[TO device]:[statements]:END SELECT COMMENTS SELECT is a Query Language command and can used on its own or with other Query Language commands to form a query section. fields can be one or more field names from one or multiple files.params can be any of the output format parameters as listed in the section which describes the ? commands. 5-140 In addition, there are three format parameters which can only be used with the SELECT command:ON file, AS heading and FIELD. The syntax and function of these parameters in described in their respective sections in Chapter 11, Volume 1. TO device specifies the device to which the query output will be sent. If it is not included output is to the screen. The device options are: TO PRINTER Outputs to the printer. TO FILE file Creates a new '.sbf' file on disk under the file name specified, using the query output. TO file Outputs to the ASCII file on disk specified by file. statements can be other query language statements formed with the commands REPORT, WHERE, and ORDER. END SELECT is used to indicate the end of a query section. It is not always necessary to include END SELECT, but you must provide Superbase with some indication of where the query section finishes and where the rest of the program starts. Otherwise, the statements following the last line in the query section will be interpreted as part of a multi-line SELECT statement. As an alternative to END SELECT, you could use a blank line. 5-141 SER PURPOSE Returns the total number of records that have been created in a file. SYNTAX SER (filename) COMMENTS You can use the SER function to assign a serial number to each record in a file. To do this, you need to define a field which will hold the serial number. It should be defined as a constant field and should have SER ("filename") as its constant formula. When you create the first record, it will be given the value 1. This value will then be incremented by one for each record you add to the file. The difference between SER and RECCOUNT is that SER gives the total number of records that have been created, irrespective of whether they have been deleted later. RECCOUNT returns the number of records currently in the file. 1 ? SER ("Stock") 2 BLANK Recno.Stock=SER ("STOCK") Price.Stock=14.95 Description.Stock="Widget" STORE SET PURPOSE Reads a text file and executes any commands in the file, or assigns variables from a text file. SYNTAX SET filename COMMENTS This command reads in a text file and executes it as if it were a sequence of 5-142 command. The file, therefore, must contain valid DML commands. If the file holds a set of variables-which have previously been saved to disk by ? MEMORY-the variable assignments are executed. This provides a way of transferring variables between different programs when CHAIN is not appropriate. For example, Program 'a' can set up variables for Program 'c', but Program 'a' CHAINs to Program 'b'. Another application would be to communicate variables between different programs which are run on different days. EXAMPLES 1 ...... process a ...... OPEN "aaa" FOR OUTPUT:? MEMORY:CLOSE OUTPUT:DISPLAY another program SET "aaa" ...... process b 2 SET "abc" 'abc' could be ASCII file which contains a set of function key assignments. 5-143 SET BUFFERS PURPOSE Sets the number of buffers Superbase uses as a disk cache. SYNTAX SET BUFFERS nexp COMMENTS Operates in the same way as the Buffers option in Set Menu Options, and allocates 512 bytes memory space for each buffer. nexp can have a value from 1 to 99. EXAMPLES 1 SET BUFFERS 24 SET PAGING PURPOSE Sets paging on or off. SYNTAX SET PAGING[ON/OFF] COMMENTS When used without ON/OFF, it acts as toggle and operates in the same way as the SET-PAGING menu options. With ON or OFF, it sets paging accordingly. EXAMPLES 1 SET PAGING OFF 5-144 SET VIEW MODE PURPOSE Sets the view mode or switches between one of the view modes and a Form. SYNTAX SET [TABLE]/[FORM]/[RECORD] COMMENTS Works in the same way as the equivalent Set Menu option, except that it does not automatically display a record (use VIEW). If a Form (as opposed to Form view) is displayed, the SET command switches the Form off and selects the specified view mode. The Form remains in memory and can be switched on again by repeating the SET command for the same view mode. In other words, when a Form has been opened, the SET command toggles the current view mode on and off. EXAMPLES 1 SET FORM 2 SET TABLE:VIEW SGN PURPOSE Finds the sign of a number. SYNTAX SGN (nexpr) COMMENTS This function returns the positive value of 1 if nexpr is positive and returns the negative value -1 if nexpr is negative. 5-145 EXAMPLES 1 numfieldc=SGN (numfielda) 2 IF SGN (datefielda-datefieldb) THEN GOTO lab1 3 x%=SGN (y%) 4 x%=SGN (y% * numfielda * (datefielda-datefieldb)) 5 x%=SGN (VAL (RIGHT$ (textfielda, 5))) 6 ? SGN (x%) NOTES Example 2 tests whether datefieldb is later than datefielda SHOW PURPOSE Shows an external file. SYNTAX SHOW [field]/[strexpr] COMMENTS SHOW is the program equivalent of the camera button at the bottom of the screen. It displays a picture or text from an external file.field must be a field which holds the name of the external file, but it does not have to be a field in the current file:if you add a file name extension to the field name, you can display pictures using other open database files. As an alternative to specifying an external field, strexpr allows you to specify the name of an external file. When field or strexpr is not given, SHOW removes the picture from the screen. 5-146 SIN PURPOSE Returns the sine of an angle measured in radians. SYNTAX SIN (nexpr) COMMENTS This function returns the sine of the angle in nexpr, where the angle is measured in radians. To convert degrees to radians, multiply by 180/PI. EXAMPLES 1 numfieldc=SIN (numfielda) 2 x%=SIN (y%) 3 x%=SIN (VAL (x$)) 4 ? SIN (x%) SPACE$ PURPOSE Fills a string with a specified number of spaces. SYNTAX SPACE$ (nexpr) COMMENTS nexpr must be in the range 0 to 255. EXAMPLES 1 textfieldc=SPACE$ (10) 2 x$=SPACE$ (4) 5-147 SQR PURPOSE Returns the square root of a number SYNTAX SQR (nexpr) COMMENTS The function returns the square root of the number specified by nexpr. If nexpr is less than zero, the function returns the error message 'invalid number'. EXAMPLES 1 numfieldc=SQR (numfielda) 2 numfieldc=SQR (2 * numfielda) 3 textfieldc=STR$ (SQR (VAL (x$))) 4 x%=SQR (y%) STORE PURPOSE Stores the current record in the current file or in the file specified. SYNTAX STORE[, 0/1/2] [FILE sbfname] COMMENTS This command stores the record in memory for the current file, or for the file specified with sbfname. It is equivalent to the Save option on the Record menu. 5-148 The numeric parameters - 0, 1 or 2 - allow you to specify whether the record is stored in batch mode or in the normal way (see the section on Batch in Chapter 5, Volume 1). STORE, 1 stores the current record in batch mode. The time taken to save the record on disk will be reduced, but the data will not yet be secure:if you suffer a power loss, you will lose any record data which has been saved (in the current session) using this option. Note that STORE, 1 only turns on batch mode for the current record.STORE, 2 does not store a record but makes the file secure. Any records that have been saved previously with the STORE, 1 command will now be made safe on disk. You should always execute a STORE, 2 command after storing records in batch mode. STORE, 0 is optional and is the same as STORE on its own:Superbase makes each record secure as it is stored. If you were to turn the computer off accidentally, the most you would lose would be the data in memory. EXAMPLES 1 BLANK Firstname="John" Lastname="Roberts" Street="15 Richmond Way" ...... ...... ...... STORE 2 FOR n%=1 to 10 BLANK Firstname=Recdata$ (n%, 1) Lastname=Recadata$ (n%, 2) Street=Recdata$ (n%, 3) ...... ...... ...... STORE, 1 NEXT STORE, 2 5-149 NOTES The first example creates a new record and stores it in the normal way. Example 2 creates ten new records, reading data into the fields for each record from the array Recdata$ and storing them in batch mode. When all the records have been stored, it makes the file secure with the STORE, 2 command. STR$ PURPOSE Returns the text equivalent of a numeric expression. SYNTAX STR$ (nexpr [[, nexpri [, nexprd]]/[, numeric-format-string]]) COMMENTS STR$ converts data which is held in a numeric variable or numeric field into a text string. nexpri specifies the number of integers before the decimal point and should be set large enough to avoid overflow display. nexprd specifies the number of integers after the decimal point. The maximum numeric format in Superbase is a total of 13 integers, so nexpri plus nexprd must be less than 14. As an alternative to using nexpri and nexprd, you can specify the numeric format as a string (see NUMBASE). If these parameters are not used, the default numeric format set by Numeric Format on the Set menu or by the most recent use of NUMBASE will be taken. The complementary function to STR$ is VAL. EXAMPLES 1 textfieldc=STR$ (numfielda) 2 textfieldc-STR$ (numfielda, 5, 0) 3 x$=STR$ (165.4444, "z999999.00") 5-150 TAN PURPOSE Returns the tangent of an angle measured in radians. SYNTAX TAN (nexpr) COMMENTS The function returns the tangent of the angle in nexpr measured in radians. The complementary function of TAN is ATN. EXAMPLES 1 numfieldc=TAN (numfielda) 2 x%=TAN (y%) 3 x%=TAN (VAL (x$)) 4 ? TAN (x%) THOUSECS PURPOSE Takes a numeric value and returns the number of thousandths of a second left over after subtracting the number of seconds. SYNTAX THOUSECS (nexpr) COMMENTS nexpr will usually contain a time in milliseconds from a time field or the result of a TIMEVAL calculation. THOUSECS returns the same result as nexpr MOD 1000. 1 x%=THOUSECS (timefield) 5-151 TIME$ PURPOSE Returns the time in string format from a numeric value which gives the time in thousandths of a second. SYNTAX TIME$ (nexpr [, timeformat]) COMMENTS The second argument for this function, timeformat, allows you to specify the format the time string will have. It must conform to the rules for Superbase time formats given in the keyword entry for DATEBASE. EXAMPLES 1 x$=TIME$ (timefield) 2 ? TIME$ (NOW, "hh:mm:ssam") TIMEVAL PURPOSE Returns the value of a time string in thousandths of a second. SYNTAX TIMEVAL (strexpr) COMMENTS strexpr must contain the time in a valid time format. See the keyword entry for DATEBASE for a list of acceptable time formats. EXAMPLES 1 t%=TIMEVAL ("10:22am") 2 t%=TIMEVAL ("14:03:12.201") 5-152 TODAY PURPOSE Gives the system date. SYNTAX TODAY COMMENTS TODAY shows the date in the date format as set with Date Format option in the Set menu, or as set by the DATEBASE command. If your computer has a real-time clock or you have set the system date, TODAY gives the current date. Otherwise, it gives the default system date. TODAY holds the date as a julian date number. Superbase automatically translates this into the current date format when you display the date using ? TODAY. EXAMPLES 1 ? TODAY 2 datefield=TODAY 3 ? MONTH (TODAY) 5-153 TRIM$ PURPOSE Trims trailing spaces from a string or a text field. SYNTAX TRIM$ (strexpr) COMMENTS This returns the string consisting of the original string specified by strexpr with any trailing spaces eliminated. EXAMPLES 1 stringfieldc=TRIM$ (textfielda) 2 x$=TRIM$ (textfieldc.filea) 3 ? LEN (x$); LEN (TRIM$ (x$) UCASE$ PURPOSE Returns the upper case equivalent of a text string or a text field. SYNTAX UCASE$ (strexpr) COMMENTS UCASE$ returns the upper case equivalent of the lowercase alphabet; no other characters, including those already in upper case, are affected. The complementary function of UCASE$ is UCASE$. EXAMPLES 1 textfieldc=UCASE$ (textfielda) 2 x$=UCASE$ (y$) 5-154 3 x$=UCASE$ ("ABCDEF") NOTES If you wish to set the first letter of a string to upper case, leaving the rest in lower case, you can so using the FCASE$ function. UPDATE PURPOSE Performs a relational update. SYNTAX UPDATE[calclist] [WHERE conditions] [END UPDATE] COMMENTS UPDATE on its own runs the update in memory. This may have been loaded from disk with the LOAD UPDATE command, or it may have been created in the same session using the Process menu option Update Edit. By specifying calclist and conditions, you can also use UPDATE to define an update and then run it. calclist corresponds to command line set in the Update Fields dialog; conditions corresponds to the filter which is set in the Update Filter dialog. The first specifies how the records are updated, the second specifies which records are to be updated. WHERE conditions and calclist should be entered as separate statements, either on the same line as UPDATE separated by colons, or on separate lines. They form part of an Update program section, headed by the UPDATE command and ending with END UPDATE. The END UPDATE command must be included if the Update section is followed by other statements in a program. Otherwise Superbase will regard these as belonging to the Update section. As an alternative to using this command, you can terminate the section with a colon or a blank line. UPDATE is a multi-file command, so both conditions and calclist can refer to more than one file. In this case, the first condition in the update filter must establish a join between two files. 5-155 EXAMPLES 1 LOAD UPDATE "Newrate":UPDATE Loads the Update file Newrate from disk, and then runs it. 2 UPDATE Price.Orders=Price.Stock WHERE Product_Code.Orders=Product_Code.Stock AND Order_date "15 July 1987" END UPDATE Updates prices in the Orders file on the basis of the price details in the Stock file. VAL PURPOSE Returns the numeric value of a text string. SYNTAX VAL (strexpr) COMMENTS The function returns the numeric value of the number (if any) in the lefthand end of the string or substring specified in strexpr. In cases where strexpr does not contain a number or where the leftmost character of strexpr is not numeric, the function returns 0. The complementary function of VAL is STR$ EXAMPLES 1 numfieldc=VAL (textfielda) 2 numfieldc=VAL (RIGHT$ (textfielda, 8)) 3 VAL (textfielda) > 1 AND VAL (RIGHT$ (textfielda, 4) > 0 4 x%=VAL ("12.45A456") 5 x%=VAL (x$) 5-156 VIEW PURPOSE Displays the current record in the current file. SYNTAX VIEW COMMENTS Allows the user to see the current record in the current file in the view format specified by the SET view mode command. It can also be used to redisplay the current Form. WAIT PURPOSE Waits for a specified time or until a key is pressed. SYNTAX WAIT [FOR time]/[FOR nexp] [var/field] COMMENTS Wait waits for a given number of seconds (FOR nexp) or until a given time (FOR time). FOR nexp implies 'wait for nexp seconds'. FOR time implies 'wait until the system clock reached time', where the time is given in the current time format. FOR var/field implies 'wait until a key is pressed, and then place it in var or field'. If you follow WAIT with a numeric variable or numeric field, it will only accept a number. In other word, pressing any key except the keys with the digits 0 to 9, will have no effect. 5-157 EXAMPLES 1 WAIT FOR 3 Waits for 3 seconds. 2 WAIT FOR 10:20:30 Wait until 10:20 am. 3 WAIT x$ Waits for a single key stroke and puts it in x$. WHERE PURPOSE Set the filter conditions for a query or a report. SYNTAX WHERE conditions COMMENTS WHERE is the program equivalent of the Filter command line in a query definition, and can only be used within a section that is headed by the query SELECT command. You can use WHERE to set a filter on the fields selected for report output or for other query applications, such as sorting, merging files, or simply retrieving data with query that has been saved to disk. conditions takes the same form as the Filter command line in the query definition dialog (see Chapter 11, Volume 1). WHERE is a multi-file filter command - unlike the record selection command SELECT WHERE - and if it used for this purpose, the join between two files must be placed at the beginning of the statement, as in: WHERE Lastname.Clients=Lastname.Deposits Any subsidiary conditions can then be added to the line using the AND operator. EXAMPLES 1 SELECT Firstname.Clients, Lastname.Clients, Bank, Amount WHERE Lastname.Clients=Lastname.Deposits AND 5-158 Lastname.Clients LIKE "[d-3]*" ORDER Lastname.Clients END SELECT 2 WHERE Price > = 50 AND Price < = 100 3 WHERE Lastname LIKE ["a-c"*] AND NOT (Country=USA) NOTES In the first example, WHERE is used to set up a multi-file filter. It selects only those clients whose details are also stored in the Deposits file and whose last name initial falls in the range A to C. Note that file extension must be given for Lastname since the field occurs in both the Clients file and the Deposits file; the Bank and Amount fields do not require an extension name since they only occur in the Deposits file. The other two examples show WHERE in use as a single file filter command. 5-159 WHILE WEND PURPOSE Executes a series of instructions as long as the specified conditions are true. SYNTAX WHILE exp statements WEND COMMENTS WHILE and WEND set up a loop, in which the statement in between are executed repeatedly for as long as the expression following WHILE is true. When the expression is not true, execution resumes with the first statement after WEND. EXAMPLES 1 OPEN FILE "Address" SELECT FIRST WHILE NOT EOF ("Address") VIEW SELECT NEXT WEND 2 WHILE NOT EOF (*"): INPUT &1, a$:? a$:WEND YEAR PURPOSE Returns a numeric value for the year from a julian date number. SYNTAX YEAR (nexp) COMMENTS The function is only valid for dates from 1 January 1 to the end of December 9999. Consequently nexpr is only valid in the range 1 to 3652048. If nexpr is 0 then the number returned is 0. If nexpr is negative the results are unpredictable. Associated date functions are DATE$ DAY DAYS DAY$ MONTH 5-160 EXAMPLES 1 numfieldc=YEAR (datefielda) 2 numfieldc=YEAR (datefielda + 90) 3 numfieldc=YEAR (TODAY) 4 x%=YEAR (datefielda + VAL (textfielda)) 5 x%=YEAR (DAYS ("11 Jan 85") 6 ? YEAR (datefielda + 30) 7 YEAR (datefielda)=1986 NOTES Example 3 provides a calculation to insert the month number of the system date into a numeric field. Example 7 provides a filter to pick out all the dates in datefielda which fall in the year 1986. 5-161 CHAPTER 6 - QUICK REFERENCE GUIDE APPLICATION FUNCTIONS RUN [filename] Execute program, optionally loading from disk CHAIN filename Execute program without clearing variables NEW [TEXT/QUERY/UPDATE] Clear program or text area EDIT [TEXT/KEY/QUERY/UPDATE] Allow user to edit program, text, query or update LOAD [TEXT/KEY/QUERY/UPDATE] filename [, APPEND] Load program, text, file, function key list, query or update SAVE [TEXT/KEY/QUERY/UPDATE] filename [, TEXT] Save program, text file, function key list, query or update PROTECT [filename] Save the current program in encrypted form CALL function Call a user supplied function SYSTEM COMMANDS DEBUG [ON/OFF] Set or clear debug flag BREAK [ON/OFF] Set or clear user stop enabled QUIT Exit Superbase system KEY keynum [, string] Set keynum to string or clear it 6-1 LIST filename List any system file to screen DELETE filename Delete any system file RENAME old filename [, TO] new filename COPY from filename [, TO] to filename Copy any system file NUMBASE string Set default numeric format DATEBASE string Set default date format DIRECTORY path Change directory to path SET TABLE/RECORD/FORM Set view according to parameter SET PAGING [ON/OFF] Set paging SET BUFFERS nexp Set number of cache buffers to use BASIC STATEMENTS [LET] var/field=exp Assign value of expression to variable or field ERASE varlist Remove a variable from memory CLEAR Clear all system variables READ var/field [, var/field] Read data into variables or fields from data pointer 6-2 DATA constant [, constant]... Specify data for READ statement RESTORE [label] Move data pointer to specified position or home REM text Non executable comment line EXECUTE string Execute text string as though command DIM array variable Set array dimensions CONTROL FLOW FOR var=nexp TO nexp [STEP nexp]statements NEXT [var] Repeat program lines a number of times GOSUB label Call a procedure or subroutine GOTO label Call a procedure or subroutine GOTO label Branch to the specified label ON ERROR [GOTO label] Specify procedure to be followed on error condition ON nexp GOTO label [, label]... Branch to statement or label in list ON nexp GOSUB label [, label]... Call procedure or subroutine in list RETURN Return from procedure or subroutine execution RESUME [NEXT / label] Resume execution after error at next or specified position END Terminate execution of application 6-3 WHILE expr Perform following commands if expression true WEND Mark end of while command sequence CONDITIONALS IF exp THEN statement/label [ELSE statement/label] Conditional statement or expression execution varfield=exp ? expr:expr Conditional assignment of value to field or variable FILE AND INDEX COMMANDS CREATE sbfname; passwords Create a new database file in memory CREATE INDEX ON exp [FILE sbfname] [TO index] [UNIQUE] Create a new index file optionally make unique ADD [FILE sbfname] field definition string [, formula string] Add a new field to a file MAKE sbfname Make the file exist on disk and store the file def MODIFY field [, ] field definition string [, formula string] Modify parameters for field changing name, type etc. SAVE FILE sbfname Save the current file definition PASSWORD sbfname; passwords Set new passwords for a specified file REORGANIZE [FILE sbfname] [TO] pathname Reorganize current or specified file to new pathname OPEN FILE sbfname; passwords Open file set as default 6-4 CLOSE [ALL]/[FILE sbfname] Close all or specified files FILE sbfname Select the default file to be used INDEX index Select the default index to be used for a file REMOVE FILE sbfname Remove all data, file and all indices REMOVE INDEX index Remove the specified index for specified file OPEN FIELDS [FILE sbfname] fieldlist Open a set of fields for specified file CLOSE FIELDS [FILE sbfname] Close the open fields for specified file RECORD COMMANDS BLANK [FILE sbfname] Clear all data from current record STORE [0/1/2, ] [FILE sbfname] Store current record in the file, batch mode optional. ENTER [field/nexpr] [, nexpr2] Allow the user to enter a record in the current file VIEW View record in the current file SELECT Record selection commands SELECT FIRST [FILE sbfname] [INDEX index] Select the first record in key sequence SELECT LAST [FILE sbfname] [INDEX index] Select the last record in key sequence 6-5 SELECT NEXT [FILE sbfname] [INDEX index] Select the next record in key sequence SELECT PREVIOUS [FILE sbfname] [INDEX index] Select the previous record in key sequence SELECT CURRENT [FILE sbfname] [INDEX index] Select the current record in key sequence SELECT DUPLICATE [FILE sbfname] [INDEX index] Select the next record with the same key SELECT REMOVE [FILE sbfname] Remove the current record in selected file SELECT KEY string [FILE sbfname] [INDEX index] Select the first record with index matching string SELECT WHERE [FILE sbfname] [conditions] Select the first record matching conditions or clear conditions SELECT field parms [WHERE parms] [REPORT parms] [ORDER parms] [SAY]/[TO PRINTER/filename/FILE sbfname] [END SELECT] Query language command PROCESS COMMANDS UPDATE calc list [WHERE conditions] [END UPDATE] Perform relational update REMOVE FROM FILE sbfname WHERE conditions Remove records matching conditions IMPORT filename [[TO] FILE sbfname] [WHERE conditions] [USING parms] Import external text file to superbase EXPORT [FILE sbfname] [INDEX index] [TO] filename [WHERE conditions [USING parms] Export to external file LABELS [FILE sbfname] [WHERE conditions] [USING labelstring] Print labels as per label definition 6-6 MERGE [TEXT filename [WHERE conditions] Load text file and mail merge INPUT OUTPUT FUNCTIONS SHOW field/strexpr Show external field OPEN filename FOR INPUT/OUTPUT/APPEND/COMM Open sequential file for input/output POSITION nexp Position in sequential file INPUT [&nexp/LINE] var/field Input characters or line from text file CLOSE INPUT/OUTPUT/COMM End input/output to/from text file or comms SET filename Read exec or variable file and execute GET var/field Get character from keyboard no wait WAIT FOR time/FOR nexp/var/field Wait till time, for no of secs or for single key entry ASK [string] [pos] [length] ;var/field Get input string from user BELL Ring bell HOME Move screen output position to home CLS Clear output screen EJECT [nexp] Do new pages on print device 6-7 SCRDUMP Do screen dump to printer LOCATE coordinates Set position on output device NEWLINE [nexp] Send newline to output device MENU column, item, state [, text] Set up a user defined menu MENU CLEAR Clear user-defined menu MENU ON numvar Turn on user-defined menus, specify variable for return value REQUEST text[, ] text[, ] type [, numvar [, textvar [, len]]] Set up a user-defined dialog (requester) MEMORY List of variables in memory STATUS [FILE sbfname] Status of selected file or system SAY [[USING pitch, mode, rate, sex, phonemes] string [, /; string...]] Amiga only. Output string as speech FG nexp Set foreground colour BG nexp Set background colour UL [ON/OFF] Set or clear underline IT [ON/OFF] Set or clear italics BF [ON/OFF] Set or clear bold face 6-8 ATTR OFF Clear bold face italics and underline ? /DISPLAY/PRINT/OUTPUT TO file Send information to selected output device ? SAY Use narrator device to speak output ? MEMORY List of variables in memory ? LIST Program listing in memory ? STATUS [FILE sbfname] Status of selected file or system ? DIRECTORY Current directory listing ? TEXT [MERGE] Text file in memory optionally mail merging ? QUERY Current query statement ? exprlist Any expression list REPORTING HEADING statements END HEADING Specify statements to execute on page heading FOOTING statements END FOOTING Specify statements to execute on page footing REPORT total list Set report totals, means and count BEFORE REPORT Specify before report activity 6-9 AFTER REPORT Specify after report activity END REPORT End of report specifications GROUP field total list Specify subtotal break field and subtotals, means and counts for group BEFORE GROUP statements Specify before group activity AFTER GROUP statements Specify after group activity END GROUP End of group specifications FORM HANDLING CLOSE FORM Close current form OPEN FORM formname Load a form into memory FORM [, page [, row [, column]]]] Specify page for current and top left-hand corner ENTER [FORM view name] [field list Enter data into fields through view form OPERATORS ARITHMETIC OPERATORS ^ Exponentiation - Negation 6-10 * Multiplication / Division MOD Modulo arithmetic + Addition - Subtraction RELATIONAL OPERATORS = Equality LIKE Pattern matching case insensitive equality <> Inequality < Less than > Greater than <= Less than or equal to >= Greater than or equal to 6-11 LOGICAL OPERATORS NOT AND OR MATHEMATICAL FUNCTIONS SGN (x) Sign of variable INT (x) Integer portion of variable ABS (x) Absolute value of variable SQR (x) Square root RND (x) Random number LOG (x) Logarithm EXP (x) Exponent COS (x) Cosine SIN (x) Sine TAN (x) Tangent ATN (x) Arctangent FIX (x, y) Fix decimal precision of value 6-12 FREE (n) Return free memory size DISKSPACE ("disk") Return free disk space RECCOUNT (sbfname) Return number of records in file SER (sbfname) Return serial number of specified file ROW (0) Return current screen row COL (0) Return current screen column PROW (n) Return current printer row PCOL (n) Return current printer column EOF (sbfname) Return if at end of file FOUND (sbfname) Return result of last search LOOKUP (value, fld) Return if value exists in file (indexed field) STRING FUNCTIONS LEN (x$) Length of string STR$ (x[[, y] [, z] / [, numformat]]) String from number with optional format VAL (x$) Value of string 6-13 ASC (x$) Ascii value of character CHR$ (x$) String value of character LEFT$ (x$, nexp) Left portion of string RIGHT$ (x$, nexp) Right portion of string MID$ (x$, nexp [, nexp]) Mid portion of string DAYS (x$) Numeric value of date DATE$ (nexp [, dform]) Date string from numeric using optional format DAY (date) Numeric day value of date DAY$ (date) Day of week from date MONTH (date) Numeric month value of date MONTH$ (date) Month string from date YEAR (date) Numeric year value of date TIMEVAL (time) Numeric value of time TIME$ (nexp [, tformat]) Time string from numeric using optional time format HRS (time) Number of hours from time 6-14 MINS (time) Number of minutes from time SECS (time) Number of seconds from time THOUSECS (time) Number of thousandths of second from time LCASE$ (x$) Convert string to lower case UCASE$ (x$) Convert string to upper case FCASE$ (x$) Capitalize first letter of string TRIM$ (x$) Trim trailing spaces from x$ LTRIM$ (x$) Trim leading spaces from x$ INSTR ([n, ]x$, y$) Find position of substring y$ in x$ REPLICATE (x$, nexp) Replicate character expression n times SPACES$ (n) Return string with n spaces ERR$ (n) Returns error message for error number n VARIABLES TODAY Return system date NOW Return system time 6-15 ERRNO Return current error number PI Return value of pi FIELDS BY NAME Fieldname; Field.file; Field. "file" MULTIPLE RESPONSE FIELDS Fieldname (nexp) STRING VARIABLES X$ NUMERIC VARIABLES X% ARRAYS X% or X$ (nexp[[, nexp] [, nexp]]) 6-16 INDEX & 5-4 C ? 5-8 Changing the output device 5-3 ? Commands 5-3 Command line 3-6 ? DIRECTORY 5-9 Editing 3-7 ? LIST 5-9 Constants 2-13 ? MEMORY 5-10 CONTAINS 2-11 ? QUERY 5-10 ? STATUS 5-11 D ? STATUS FILE 5-11 DATA 5-33, 5-116 ? TEXT 5-12 Date 2-6 @ 5-4 Date format 2-6 DATE$ 5-34 A DATEBASE 5-35 ABS 5-13 DAY 5-36 ADD 5-14 DAY$ 5-37 AFTER GROUP 5-17 DAYS 5-38 AFTER REPORT 5-18 DELETE 5-39 APPEND 5-83, 5-103 DESCENDING 5-106 Arithmetic operators 2-7 DIRECTORY 5-40 Arrays 2-4 See ? DIRECTORY ASC 5-18 DISKSPACE 5-41 ASCENDING 5-106 DOWN 5-4 ASK 5-19 ATN 5-20 E ATTR 5-4 EDIT 5-42 Editing keys B Command line 3-7 Batch mode 5-148 Program 3-5 BEFORE GROUP 5-21 EJECT 5-43 BELL 5-22 END 5-44 BF 5-4 END GROUP 5-44 BG 5-4 END HEADING 5-45 BLANK 5-22 END REPORT 5-45 BREAK ON/OFF 5-23 EOF 5-48 ERASE 5-49 PAGING 5-144 TO FILE 5-141 RECORD 5-145 TO PRT 5-141 TABLE 5-145 TODAY 5-153 View mode 5-145 TRIM$ 5-154 SGN 5-145 SHOW 5-146 U SIN 5-147 UCASE$ 5-154 Sorting data 5-106 UL 5-4 SPACE$ 5-147 Update 5-155 SQR 5-148 Load 5-83 Start up program 3-9 Save 5-130 STATUS See ? STATUS V STORE 5-148 VAL 5-156 STR$ 5-150 Variables 2-2 String variables 2-4 Arrays 2-4 Syntax 2-15 Names 2-2 System status 5-11 Numeric 2-3 System variables 2-4 String 2-4 System 2-4 T VIEW 5-157 TAN 5-151 Ternary operator 5-81 W Text WAIT 5-157 Load 5-83 WEND 5-160 Outputting files 5-12 WHERE 5-158 Save 5-130 WHILE 5-160 THOUSECS 5-151 Time 2-6 Y TIME$ 5-152 YEAR 5-160 TIMEVAL 5-152 ============================================================================ DOCS PROVIDED BY RAP AND -+*+-THE SOUTHERN STAR-+*+- for M.A.A.D. ============================================================================